First of all: daylight savings time sucks. Now that I've gotten that off
my chest, I have a question. I ran across something that I know is
theoretically correct but is not functionally correct (for me at least) and
I wanted some more input. Basically, when I select a timestamp from the
table, the timezone (based on daylight savings time) that is returned is
the one based on the time itself. So, if it is currently November 5th and
Mountain Standard Time (MST), and I select a timestamp that is
in...September let's say (that's Mountain Daylight Time (MDT)), it will
return the time in MDT. What I'd like it to do is return the time in MST,
the time I'm currently in.
If you don't understand why I would want it that way, here's an example.
So, I've got a calendar application that allows me to insert events and the
system emails me a reminder at a certain time before the event (1 hour, 2
hours, 1 day, etc). If the current date is October 20th (before the
Daylight Savings Time change on the 28th) and I'm inserting calendar events
for November, once Daylight Savings Time occurs, all of my events will be
off by one hour.
My question: is there a good way to deal with all of this?
My answer: the only thing I could come up with was to patch PostgreSQL so
that when it encodes the timestamp to be displayed, it checks the tm_isdst
field of the time structure for the current time, not the stored time.
I've made an option called "now_zone" to enable/disable this on the fly.
Here's an example:
www=# select start_date, end_date from event_recur where eid = 452;
start_date | end_date
------------------------+------------------------
2001-09-16 00:00:00-06 | 2001-12-16 00:00:00-07
(1 row)
www=# set now_zone = true;
SET VARIABLE
www=# select start_date, end_date from event_recur where eid = 452;
start_date | end_date
------------------------+------------------------
2001-09-16 00:00:00-07 | 2001-12-16 00:00:00-07
(1 row)
If anyone wants to take a look at this patch, it's located here:
http://www.superiorcomm.net/files/now_zone.patch
I would really appreciate some comments/feedback on whether or not my patch
is a good idea or if there's a better way to handle this. Thanks!
--
PC Drew
Superior Communications
1455 Dixon Ave, Suite 310
Lafayette, CO 80026
pc@superiorcomm.net
720-841-4543
